
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spBulkOrdersGetListBySortType]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [spBulkOrdersGetListBySortType]
GO

CREATE PROCEDURE spBulkOrdersGetListBySortType
	@SortType int,	
	@Type int,	
	@supplier varchar(3),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TempBulkOrders (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	BulkOrderID int	
)

DECLARE @sql nvarchar(2000)

-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TempBulkOrders ([BulkOrderID]) SELECT '
IF (@PageSize > 0)
	SET @sql = @sql + ' TOP ' + CAST(@Page*@PageSize as nvarchar) 

SET @sql = @sql + ' [BulkOrderID] FROM [BulkOrders] WHERE SupplierCode = ''' + @supplier + ''''
			+ ' AND ((0=' + CAST(@Type as nvarchar) + ' AND IsArchived=0 AND CompletedDate is NULL) 
			OR (CompletedDate is not null AND IsArchived=0 AND 1=' + CAST(@Type as nvarchar) + ')
			OR (IsArchived=1 AND 2=' + CAST(@Type as nvarchar) + ')
			)'
IF @SortType = 0 
	SET @sql = @sql + ' ORDER BY [Type], Owner, ProductCode'
ELSE IF @SortType = 1 
	SET @sql = @sql + ' ORDER BY Owner, ProductCode '
ELSE IF @SortType = 2 
	SET @sql = @sql + ' ORDER BY ProductCode, Owner'				
ELSE IF @SortType = 3 
	SET @sql = @sql + ' ORDER BY Owner, BONo, ProductCode'				 

EXEC (@sql)

SELECT @TotalRecords = COUNT(*) FROM [BulkOrders] 
WHERE suppliercode = @supplier
AND ((@Type=0 AND IsArchived=0 AND CompletedDate IS NULL) 
OR (@Type=1 AND IsArchived=0 AND CompletedDate IS NOT NULL)
OR (@Type=2 AND IsArchived=1)
)

SELECT
	[BulkOrders].[BulkOrderID],
	[OrderDate],
	[Type],
	[ProductCode],
	[Description],
	[Color],
	[StartDate],
	[TotalQtyOrdered],
	[TotalQtyCompleted],
	[TotalQtyInProgressBelow50P],
	[TotalQtyInProgressAbove50P],
	[EstimatedDate],
	[StockLastCheckedDate],
	[StockLastCheckedQty],
	[FOB],
	[CompletedDate],
	CASE [Type]
		WHEN 1 THEN
			([TotalQtyOrdered] - ([TotalQtyCompleted]+ISNULL([TotalQtyInProgressBelow50P],0)+ISNULL([TotalQtyInProgressAbove50P], 0)))
		ELSE
			NULL
	END TotalQtyNotStarted,
	[SupplierCode],
	[Owner],
	Catalogs.CatalogName as [Catalogue],
	[Page],
	[Pack],
	[PackQty],
	[BulkOrders].[CatalogID],
	[Target],
	[SalesDescription1],
	[SalesDescription2],
	[MinOrderQty],
	[TallPaletQty],
	[StockLocation],
	[AvailableInUSA],
	BONo,
	(SELECT SortOrder FROM IDSSorts WHERE IDSSorts.IDSSortID=BulkOrders.IDSSortID) as IDSSortOrder,	
	[IsArchived],
	[CatPackDesc],
	[MUKTHGCatPackQty],
	[MUKTHGCatPackDesc],
	[HomeDelivered15Price],
	[IsShownInLGDCatalog],
	[SpecialOfferDisc],
	[ModifiedDate],
	(SELECT Code FROM Currency WHERE [BulkOrders].[CurrencyID] = Currency.[CurrencyID]) as CurrencyCode,
	([TotalQtyCompleted] - ISNULL((SELECT SUM(QtyIssued)
					FROM DeliveredOrders 
					WHERE [BulkOrders].[BulkOrderID] = DeliveredOrders.[BulkOrderID]), 0)) as QuantityAvailable,
					
	([TotalQtyCompleted] - ISNULL((SELECT SUM(QtyIssued)
									FROM DeliveredOrders LEFT JOIN [ShippingStatusReport] ON [ShippingStatusReport].[ID] = DeliveredOrders.[POID]
									WHERE [BulkOrders].[BulkOrderID] = DeliveredOrders.[BulkOrderID]
									AND [ShippingStatusReport].ShippingDate IS NOT NULL
									AND DATEDIFF(day, [ShippingStatusReport].ShippingDate, getdate()) >= 0), 0)) as ActualStock,
									
	(SELECT SUM(QtyIssued)
					FROM DeliveredOrders LEFT JOIN [ShippingStatusReport] ON [ShippingStatusReport].[ID] = DeliveredOrders.[POID]
					WHERE [BulkOrders].[BulkOrderID] = DeliveredOrders.[BulkOrderID]
						AND [ShippingStatusReport].ShippingDate IS NOT NULL
						AND DATEDIFF(day, [ShippingStatusReport].ShippingDate, getdate()) >= 0) as QuantityShipped,
						
	(SELECT SUM(QtyIssued)
					FROM DeliveredOrders LEFT JOIN [ShippingStatusReport] ON [ShippingStatusReport].[ID] = DeliveredOrders.[POID]
					WHERE [BulkOrders].[BulkOrderID] = DeliveredOrders.[BulkOrderID]
						AND ([ShippingStatusReport].ShippingDate IS NULL
							 OR ([ShippingStatusReport].ShippingDate IS NOT NULL 
									AND DATEDIFF(day, [ShippingStatusReport].ShippingDate, getdate()) < 0)
							)
							 ) as QuantityNotShipped,
	CASE 
		WHEN MUKTHGCatPackQty IS NULL OR MUKTHGCatPackQty <= 0 THEN NULL
		ELSE
			FLOOR(CAST(([TotalQtyOrdered] - ISNULL((SELECT SUM(QtyIssued)
					FROM DeliveredOrders 
					WHERE [BulkOrders].[BulkOrderID] = DeliveredOrders.[BulkOrderID]), 0)) as decimal) / CAST(MUKTHGCatPackQty AS Decimal))
	END OperaQty
	/*				
	(([TotalQtyCompleted] - ISNULL((SELECT SUM(QtyIssued)
					FROM DeliveredOrders 
					WHERE [BulkOrders].[BulkOrderID] = DeliveredOrders.[BulkOrderID]), 0)) 
					+ 
					(ISNULL((SELECT SUM(Amount) 
							FROM AdjustQuantity 
							WHERE [BulkOrders].[BulkOrderID] = AdjustQuantity.[BulkOrderID]), 0
							)
					 )
					 ) as AdjustedQuantity,
				 
	([TotalQtyCompleted] - ISNULL((SELECT SUM(QtyIssued)
									FROM DeliveredOrders LEFT JOIN [ShippingStatusReport] ON [ShippingStatusReport].[ID] = DeliveredOrders.[POID]
									WHERE [BulkOrders].[BulkOrderID] = DeliveredOrders.[BulkOrderID]
										AND [ShippingStatusReport].ShippingDate IS NOT NULL
										AND DATEDIFF(day, [ShippingStatusReport].ShippingDate, getdate()) >= 0), 0)
						 + ISNULL((SELECT SUM(Amount) 
									FROM AdjustQuantity LEFT JOIN [ShippingStatusReport] ON [ShippingStatusReport].[ID] = AdjustQuantity.[POID]
									WHERE [BulkOrders].[BulkOrderID] = AdjustQuantity.[BulkOrderID]
										AND [ShippingStatusReport].ShippingDate IS NOT NULL
										AND DATEDIFF(day, [ShippingStatusReport].ShippingDate, getdate()) >= 0), 0)							
					) as ActualStock,
					
	(ISNULL((SELECT SUM(QtyIssued)
					FROM DeliveredOrders LEFT JOIN [ShippingStatusReport] ON [ShippingStatusReport].[ID] = DeliveredOrders.[POID]
					WHERE [BulkOrders].[BulkOrderID] = DeliveredOrders.[BulkOrderID]
						AND [ShippingStatusReport].ShippingDate IS NOT NULL
						AND DATEDIFF(day, [ShippingStatusReport].ShippingDate, getdate()) >= 0), 0)
	 + ISNULL((SELECT SUM(Amount) 
					FROM AdjustQuantity LEFT JOIN [ShippingStatusReport] ON [ShippingStatusReport].[ID] = AdjustQuantity.[POID]
					WHERE [BulkOrders].[BulkOrderID] = AdjustQuantity.[BulkOrderID]
						AND [ShippingStatusReport].ShippingDate IS NOT NULL
						AND DATEDIFF(day, [ShippingStatusReport].ShippingDate, getdate()) >= 0), 0)				
					) as QuantityShipped,
						
	(ISNULL((SELECT SUM(QtyIssued)
					FROM DeliveredOrders LEFT JOIN [ShippingStatusReport] ON [ShippingStatusReport].[ID] = DeliveredOrders.[POID]
					WHERE [BulkOrders].[BulkOrderID] = DeliveredOrders.[BulkOrderID]
						AND ([ShippingStatusReport].ShippingDate IS NULL
							 OR ([ShippingStatusReport].ShippingDate IS NOT NULL 
									AND DATEDIFF(day, [ShippingStatusReport].ShippingDate, getdate()) < 0)
							)
			 ), 0)
	+ ISNULL((SELECT SUM(Amount) 
				FROM AdjustQuantity LEFT JOIN [ShippingStatusReport] ON [ShippingStatusReport].[ID] = AdjustQuantity.[POID]
				WHERE [BulkOrders].[BulkOrderID] = AdjustQuantity.[BulkOrderID]
					AND ([ShippingStatusReport].ShippingDate IS NULL
							 OR ([ShippingStatusReport].ShippingDate IS NOT NULL 
									AND DATEDIFF(day, [ShippingStatusReport].ShippingDate, getdate()) < 0)
						)
			  ), 0)				
							 ) as QuantityNotShipped,
	CASE 
		WHEN [MUKTHGCatPackQty] IS NULL OR [MUKTHGCatPackQty] <= 0 THEN NULL
		ELSE
			FLOOR(CAST(
					(([TotalQtyCompleted] - ISNULL((SELECT SUM(QtyIssued)
					FROM DeliveredOrders 
					WHERE [BulkOrders].[BulkOrderID] = DeliveredOrders.[BulkOrderID]), 0)) 
					+ 
					(ISNULL((SELECT SUM(Amount) 
					 FROM AdjustQuantity 
					 WHERE [BulkOrders].[BulkOrderID] = AdjustQuantity.[BulkOrderID]), 0))) as decimal) / CAST([MUKTHGCatPackQty] AS Decimal))
	END OperaQty
	*/
	/*			
				 
	CASE 
		WHEN PackQty IS NULL OR PackQty <= 0 THEN NULL
		ELSE
			FLOOR(CAST(([TotalQtyOrdered] - ISNULL((SELECT SUM(QtyIssued)
					FROM DeliveredOrders 
					WHERE [BulkOrders].[BulkOrderID] = DeliveredOrders.[BulkOrderID]), 0)) as decimal) / CAST(PackQty AS Decimal))
	END OperaQty
	*/
	/*
	([TotalQtyOrdered] - [TotalQtyCompleted] + 
					([TotalQtyCompleted] - ISNULL((SELECT SUM(QtyIssued)
											FROM DeliveredOrders 
											WHERE [BulkOrders].[BulkOrderID] = DeliveredOrders.[BulkOrderID]), 0))) 
					as OperaQty
	*/
FROM
	#TempBulkOrders AS tblTemp JOIN [BulkOrders] ON
	tblTemp.BulkOrderID = [BulkOrders].BulkOrderID 	
	LEFT JOIN Catalogs ON [BulkOrders].CatalogID = Catalogs.CatalogID
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TempBulkOrders

GO

	
 